
package com.whcoding.easypoi.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.whcoding.easypoi.vo.OrmDeptExcelVO;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;

/**
 * 参考:https://blog.csdn.net/qq_31984879/article/details/91611713
 * https://segmentfault.com/a/1190000040869466
 * https://segmentfault.com/a/1190000039233170
 */
public class POIUtil {

	public static void main(String[] args) throws Exception {
		//
		String filePath = "D:\\360极速浏览器下载\\部门信息明细.xlsx";
		readExcel(filePath);
	}

	public static void readExcel(String fileName) throws Exception {

		InputStream is = new FileInputStream(new File(fileName));
		Workbook hssfWorkbook = null;
		if (fileName.endsWith("xlsx")) {
			// Excel 2007
			hssfWorkbook = new XSSFWorkbook(is);
		} else if (fileName.endsWith("xls")) {
			// Excel 2003
			hssfWorkbook = new HSSFWorkbook(is);

		}
		List<OrmDeptExcelVO> list = new ArrayList<>();
		// 1.循环工作表Sheet 多个sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			//2.获取sheet数据
			Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			int rows = hssfSheet.getPhysicalNumberOfRows();
			// 循环行Row 行数从0开始
			for (int rowNum = 0; rowNum < rows; rowNum++) {
				//3.获取每行row的数据
				Row row = hssfSheet.getRow(rowNum);
				//4.获取列的个数
				int coloumNum = row.getPhysicalNumberOfCells();
				OrmDeptExcelVO deptExcelVO = new OrmDeptExcelVO();

				//5.循环列
				for (int j = 0; j < coloumNum; j++) {
					//获取首行列名称
					String firstRowColumnStr = hssfSheet.getRow(0).getCell(j).toString();
					//获取第rowNum行的地j个单元格
					Cell cell = hssfSheet.getRow(rowNum).getCell(j);
					//获取单元格值
					String valueCell = getCellValue(cell);
					if (firstRowColumnStr.equals("客户名称")) {
						deptExcelVO.setDeptName(valueCell);

					} else if (firstRowColumnStr.equals("客户编码")) {
						deptExcelVO.setDeptNo(valueCell);
					}

				}
				list.add(deptExcelVO);
			}
		}
		if (!CollectionUtils.isEmpty(list)) {
			list.forEach(System.out::println);
		}
	}

	/**
	 * 解析单元格
	 *
	 * @param cell
	 * @return
	 */
	private static String getCellValue(Cell cell) {
		//格式就
		DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
		DecimalFormat df = new DecimalFormat("0.0001");
		String value = "";
		switch (cell.getCellType()) {
			case NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					//日期型
					value = fmt.format(cell.getDateCellValue());
				} else {
					//数字型
					value = df.format(cell.getNumericCellValue());
					value = value.replaceAll("0+?$", "").replaceAll("[.]$", "");
				}
				break;
			case STRING:
				value = cell.getStringCellValue();
				break;
			case BOOLEAN:
				value = String.valueOf(cell.getBooleanCellValue());
				break;
			case BLANK:
				value = cell.getStringCellValue();
				break;
			case ERROR:
				value = "";
				break;
			case FORMULA:
				//公式
				try {
					value = String.valueOf(cell.getStringCellValue());
				} catch (IllegalStateException e) {
					value = String.valueOf(cell.getNumericCellValue());
				}
				break;
			default:
				value = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
		}
		return value;
	}


}